﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Data;
using System.Collections;

/// <summary>
/// Summary description for ManageUsers
/// </summary>
public class ManageUser
{
    private bool _validUser;

    public bool ValidUser
    {
        get { return _validUser; }
        set { _validUser = value; }
    }
    private string _firstName;

    public string FirstName
    {
        get { return _firstName; }
        set { _firstName = value; }
    }
    private string _lastName;

    public string LastName
    {
        get { return _lastName; }
        set { _lastName = value; }
    }
    private string _userName;

    public string UserName
    {
        get { return _userName; }
        set { _userName = value; }
    }
    private int _userId;

    public int UserId
    {
        get { return _userId; }
        set { _userId = value; }
    }

    private int _userLevel;

    public int UserLevel
    {
        get { return _userLevel; }
        set { _userLevel = value; }
    }

    private string _centerId;

    public string CenterId
    {
        get { return _centerId; }
        set { _centerId = value; }
    }

    private ArrayList _listOfRoles;

    public ArrayList ListOfRoles
    {
        get { return _listOfRoles; }
        set { _listOfRoles = value; }
    }


    public ManageUser()
	{
		
        
	}
    public bool isValidUser(string userName, string password)
    {
        bool bValid = false;
        string sql = @"select u.user_id UserId,u.username,u.first_name, u.last_name,
                       u.center_id, u.user_level
                     from users u
                     where u.username='" + userName + "'" +
                     "and u.password='" + password + "'" +
                     "and u.login_status='A'";
        MySqlDataReader reader = DBHelper.GetDataFromDBInReader(sql);
        if (reader != null)
        {
            if (reader.Read())
            {
                bValid = true;
                this.ValidUser = true;
                this.UserId = int.Parse(reader["UserId"].ToString());
                this.UserName = Utility.cleanseText(userName);
                this.FirstName = reader["first_name"].ToString();
                this.LastName = reader["last_name"].ToString();
                this.UserLevel = int.Parse(reader["user_level"].ToString());
                this.CenterId = reader["center_id"].ToString();

                SetUserRoleInfo();
            }
        }
        return bValid;
    }

    public DataSet SelectAllUsers(string username, string name)
    {
        string sql = @"SELECT u.user_id,u.username,u.first_name, u.last_name,
                     u.user_level,u.center_id,u.user_regdate,uap.Role_Id user_role
                     from users u, user_access_profile uap
                     where uap.user_id = u.user_id AND
                     uap.role_id > 1 AND
                     u.username Like '%" + Utility.cleanseText(username.Trim()) + "%' AND " +
                     "(u.first_name Like '%" + Utility.cleanseText(name.Trim()) + "%' OR "+
                     "u.last_name Like '%" + Utility.cleanseText(name.Trim()) + "%') ORDER BY user_id";
        DataSet results = DBHelper.GetDataFromDBInDataSet(sql);
        return results;
    }


    public DataSet SelectUserById(string user_id)
    {
        string sql = @"SELECT u.user_id,u.username,u.first_name, u.last_name,
                       u.user_level,IfNull(u.center_id,'-1')center_id,u.user_regdate,
                       u.Login_Status,uap.Role_Id user_role
                     from users u , user_access_profile uap
                     where uap.user_id = u.user_id AND
                     u.user_id=" + user_id;
        DataSet results = DBHelper.GetDataFromDBInDataSet(sql);
        return results;
    }

    public bool DeleteById(System.UInt32 user_id)
    {
        string[] sql = new string[2];
        sql[0] = "Delete From user_access_profile Where user_id = " + user_id;
        sql[1] = "Delete From users Where user_id = " + user_id;
        bool bStatus = DBHelper.InsertUpdateDataInDb(sql);
        return bStatus;
    }

    public bool CreateUser(string userName, string password, string center_Id, string first_Name, string last_Name, int user_Level, string user_role)
    {
        string status = "A";
        
        if (last_Name != null && last_Name.Trim().Length > 0)
            last_Name = "'" + Utility.cleanseText(last_Name) + "'";
        else
            last_Name = "NULL";

        string strCenterId = null;
        if (center_Id.Equals("-1"))
            strCenterId = "null";
        else
            strCenterId = "'" + center_Id + "'";

        string sql = @"Insert Into users(UserName,Password,FIRST_NAME,LAST_NAME,LOGIN_STATUS,CENTER_ID,user_level,user_regdate) 
                       Values('" + Utility.cleanseText(userName) + "','" + Utility.cleanseText(password) +
                       "','" + Utility.cleanseText(first_Name) + "'," + last_Name + ",'" + status + "'," + strCenterId + "," + user_Level + ",Now())";
        long lastInsertedId = 0;
        bool bStatus = DBHelper.InsertUpdateDataInDb(sql, ref lastInsertedId);
        if (bStatus && lastInsertedId > 0)
        {
            //Split comma separated list of roles
            char[] delim = { ',' };
            string[] arr = user_role.Split(delim);
            for (int i = 0; i < arr.Length; i++)
            {
                sql = @"Insert Into user_access_profile(Role_Id,User_Id) 
                          Values(" + arr[i] + "," + lastInsertedId + ")";
                bStatus = DBHelper.InsertUpdateDataInDb(sql);
            }
        }
        return bStatus;
    }

    public bool UpdateUser(int user_id, string userName, string center_Id, string first_Name, string last_Name, int user_Level, string user_role, string login_status)
    {
        if (last_Name != null && last_Name.Trim().Length > 0)
            last_Name = "'" + Utility.cleanseText(last_Name) + "'";
        else
            last_Name = "NULL";

        string strCenterId = null;
        if (center_Id.Equals("-1"))
            strCenterId = "null";
        else
            strCenterId = "'" + center_Id + "'";

        string sql = @"Update users Set UserName='" + Utility.cleanseText(userName) +
                     "',FIRST_NAME='" + Utility.cleanseText(first_Name) + "',LAST_NAME=" + last_Name +
                     ",LOGIN_STATUS='" + login_status + "',CENTER_ID=" + strCenterId + ",user_level=" + user_Level +
                     " Where User_Id=" + user_id;
        bool bStatus = DBHelper.InsertUpdateDataInDb(sql);
        if (bStatus)
        {
            //Split comma separated list of roles
            char[] delim = { ',' };
            string[] arr = user_role.Split(delim);
            for (int i = 0; i < arr.Length; i++)
            {
                sql = @"Update user_access_profile Set Role_Id=" + arr[i] +
                      " Where User_Id=" + user_id; 
                bStatus = DBHelper.InsertUpdateDataInDb(sql);
            }
        }
        return bStatus;
    }

    private void SetUserRoleInfo()
    {
        string sql = @"Select r.Role_Name From role_lookup as r, user_access_profile as u
                     Where r.Role_Id = u.Role_Id AND u.User_Id=" + this._userId;
        DataTable dt = DBHelper.GetDataFromDbInDataTable(sql);
        if (dt != null && dt.Rows.Count > 0)
        {
            this._listOfRoles = new ArrayList();
            foreach (DataRow roleRow in dt.Rows)
            {
                this._listOfRoles.Add(roleRow["Role_Name"].ToString());
            }
        }
    }

    public bool IsInRole(string roleName)
    {
        bool bInRole = false;
        string strRole = null;
        IEnumerator ienum = this._listOfRoles.GetEnumerator();
        while (ienum.MoveNext())
        {
            strRole = (string)ienum.Current;
            if (strRole.ToLower().Equals(roleName.ToLower()))
            {
                bInRole = true;
                break;
            }
        }
        return bInRole;
    }

    public bool IsUserNameDuplicate(string userName)
    {
        bool bDuplicate = false;
        string sql = @"Select Count(User_Id) Cnt From users Where username ='" + Utility.cleanseText(userName) + "'";
        DataTable dt = DBHelper.GetDataFromDbInDataTable(sql);
        if (dt != null)
        {
            DataRow row = dt.Rows[0];
            if (int.Parse(row["Cnt"].ToString()) > 0)
                bDuplicate = true;
        }
        return bDuplicate;
    }

    public bool IsUserNameDuplicate(string userName, string userId)
    {
        bool bDuplicate = false;
        string sql = @"Select Count(User_Id) Cnt From users Where username ='" +
                     Utility.cleanseText(userName) + "' AND User_Id !=" + userId;
        DataTable dt = DBHelper.GetDataFromDbInDataTable(sql);
        if (dt != null)
        {
            DataRow row = dt.Rows[0];
            if (int.Parse(row["Cnt"].ToString()) > 0)
                bDuplicate = true;
        }
        return bDuplicate;
    }
}
